package dao.impl;

import bean.Pest;
import dao.IPestDao;
import util.DBUTil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class PestDaoImpl implements IPestDao {
    @Override
    public List<Pest> ListByPage(int pageNo, int pageSize) {
        List<Pest> pests =new ArrayList<>();

        Connection conn=null;
        PreparedStatement ps =null;
        ResultSet rs =null;

        try {
            conn =DBUTil.getConn();
//            SELECT
//            t_pest.pkid,
//                    t_pest.f_pestName,
//                    t_pest.f_host,
//                    t_pest.f_detriment
//            FROM
//                    t_pest
            String sql ="select pkid,f_pestName,f_host,f_detriment from t_pest limit ?,?";
            ps =conn.prepareStatement(sql);
            ps.setInt(1,(pageNo-1)*pageSize);
            ps.setInt(2,pageSize);
            rs =ps.executeQuery();
            while(rs.next()){
                Pest p =null;
                int pkid =rs.getInt("pkid");
                String f_pestName =rs.getString("f_pestName");
                String f_host = rs.getString("f_host");
                String f_detriment =rs.getString("f_detriment");
                p =new Pest(pkid,f_pestName,f_host,f_detriment);
                pests.add(p);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUTil.close(rs,ps,conn);
        }

        return pests;
    }

    @Override
    public int getCount() {
        int count =0;
        Connection conn=null;
        PreparedStatement ps =null;
        ResultSet rs =null;
        try {
            conn =DBUTil.getConn();
            String sql ="select count(0) from t_pest";
            ps =conn.prepareStatement(sql);
            rs =ps.executeQuery();
            if(rs.next()){
                count =rs.getInt("count(0)");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUTil.close(rs,ps,conn);
        }
        return count;
    }

    @Override
    public int addPest(Pest p) {

        int count =-1;
        Connection conn=null;
        PreparedStatement ps =null;
        try {
            conn =DBUTil.getConn();
            String sql ="insert into t_pest(f_pestName,f_host,f_detriment) values(?,?,?)";
            ps =conn.prepareStatement(sql);
            ps.setString(1,p.getF_pestName());
            ps.setString(2,p.getF_host());
            ps.setString(3,p.getF_detriment());
            count =ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUTil.close(null,ps,conn);
        }
        return count;
    }

    @Override
    public List<Pest> ListByPage(int pageNo, int pageSize, String f_pestName, String f_host) {
        List<Pest> pests =new ArrayList<>();

        Connection conn=null;
        PreparedStatement ps =null;
        ResultSet rs =null;

        try {
            conn =DBUTil.getConn();
            String sql ="select pkid,f_pestName,f_host,f_detriment  " +
                    "from t_pest where 1=1 ";

            if(f_pestName!=null){
                sql=sql+" and f_pestName like '%"+f_pestName+"%' ";
            }
            if(f_host!=null){
                sql =sql +" and f_host like '%"+f_host+"%'";
            }
            sql+=" limit "+(pageNo-1)*pageSize+","+pageSize+"";
            ps =conn.prepareStatement(sql);

            rs =ps.executeQuery();
            while(rs.next()){
                Pest p =null;
                int pkid =rs.getInt("pkid");
                String f_pestName2 =rs.getString("f_pestName");
                String f_host2 = rs.getString("f_host");
                String f_detriment =rs.getString("f_detriment");
                p =new Pest(pkid,f_pestName2,f_host2,f_detriment);
                pests.add(p);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUTil.close(rs,ps,conn);
        }

        return pests;
    }

    @Override
    public int getCount(String pName, String pHost) {
        int count =0;
        Connection conn=null;
        PreparedStatement ps =null;
        ResultSet rs =null;
        try {
            conn =DBUTil.getConn();
            String sql ="select count(0) from t_pest where 1=1 ";
            if(pName!=null){
                sql=sql+" and f_pestName like '%"+pName+"%' ";
            }
            if(pHost!=null){
                sql =sql +" and f_host like '%"+pHost+"%'";
            }
            ps =conn.prepareStatement(sql);
            rs =ps.executeQuery();
            if(rs.next()){
                count =rs.getInt("count(0)");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUTil.close(rs,ps,conn);
        }
        return count;
    }
}
